#encoding: utf-8

=begin
    用于产生丰巢有机商店迁移到微盟商店的用户数据表
    同时将用户的余额和积分流水冻结
=end

load 'rds_api.rb'

def get_recs
    recs = []
    sql = 'select o.uid, u.account, u.now_money, u.real_name, u.integral, count(o.id) as total_orders, sum(o.total_price) as total_paid  from crmeb.eb_store_order o left join crmeb.eb_user u on o.uid = u.uid where o.paid > 0 and o.mer_id = 1 group by uid;'
    res = queryRds sql
    res.each do |r|

        info = r
        uid = r['uid']

        #取最新的一张订单名字和电话
        sql = "select real_name, user_phone, user_address from crmeb.eb_store_order where uid = #{uid} and paid > 0 and mer_id =1 order by id desc limit 1;"
        oes = queryRds sql
        misc =queryRds sql
        misc.each do |m|
            info.store 'name', m['real_name']
            info.store 'phone', m['user_phone']
            splitted = m['user_address'].split(' ')
            info.store 'province', splitted[0].to_s
            info.store 'city',  splitted[1].to_s
            info.store 'zone', splitted[2].to_s
            info.store 'address', splitted[3].to_s
        end

        #获取平台会员等级信息
        sql = "SELECT max(level_id) as level, from_unixtime(valid_time) as valid from crmeb.eb_user_level where mer_id = 0 and uid = #{uid}";
        misc =queryRds sql
        misc.each do |m|
            break if m['level'].nil?
            info.store 'level', m['level']
            info.store 'valid', m['valid']
        end

        #获取商店会员卡等级
        sql = "SELECT max(level_id) as level, from_unixtime(valid_time) as valid from crmeb.eb_user_level where mer_id = 1 and uid = #{uid}";
        misc =queryRds sql
        misc.each do |m|
            break if m['level'].nil?
            info.store 'level', m['level']
            info.store 'valid', m['valid']
        end

        recs += [info]

    end

    return recs

end

#似乎在登记money变化时候出错了
def clear_bonus
    recs = []
    sql = 'select o.uid, u.account, u.now_money, u.real_name, u.integral, count(o.id) as total_orders, sum(o.total_price) as total_paid  from crmeb.eb_store_order o left join crmeb.eb_user u on o.uid = u.uid where o.paid > 0 and o.mer_id = 1 group by uid;'
    res = queryRds sql
    res.each do |r|
        info = r
        uid = r['uid']
        recs += [info]
        
        bonus = r['integral'].to_f.round(2)
        money = r['now_money'].to_f.round(2)


        if bonus > 0
            usql = "INSERT INTO crmeb.eb_user_bill (`uid`, `link_id`, `pm`, `title`, `category`, `type`, `number`, `balance`, `mark`, `add_time`, `status`, `take`) VALUES (#{uid}, '666', 0, '迁移到乐土生活商店', 'integral', 'system_sub', #{bonus}, 0.00, '系统迁移扣除#{bonus}积分', #{Time.now.to_i}, 1, 0);"
            queryRds usql
            usql = "update crmeb.eb_user set integral =0 where uid = #{uid}"
            queryRds usql
            print('.')
        end

        if money > 0
            usql = "INSERT INTO (`uid`, `link_id`, `pm`, `title`, `category`, `type`, `number`, `balance`, `mark`, `add_time`, `status`, `take`) VALUES (#{uid}, '666', 0, '迁移到乐土生活商店', 'now_money', 'system_sub', #{money}, 0.00, '系统扣除了#{money}余额', 1594363936, 1, 0);"
            usql = "update crmeb.eb_user set now_money =0 where uid = #{uid}"
        end

    end

    return recs

end

def clear_money_by_excel xlsx

        s = SimpleSpreadsheet::Workbook.read xlsx
        s.selected_sheet = s.sheets.first
        line_idx = 0
        updated_count = 0

        s.first_row.upto(s.last_row) do |line|

            line_idx += 1
            next if line_idx == 1
            uid = s.cell(line,1)
            money = s.cell(line,3)
            phone = s.cell(line,9)
            money = money.to_f.round(2)

            if money > 0
                sqls = []
                sqls += ["INSERT INTO crmeb.eb_user_bill(`uid`, `link_id`, `pm`, `title`, `category`, `type`, `number`, `balance`, `mark`, `add_time`, `status`, `take`) VALUES (#{uid}, '666', 0, '迁移到乐土商店#{phone}', 'now_money', 'system_sub', #{money}, 0.00, '迁移到乐土商店扣除#{money}元余额', #{Time.now.to_i}, 1, 0);"]
                sqls += ["update crmeb.eb_user set now_money =0 where uid = #{uid};"]
                commitTrans sqls
                print '.'
            end

        end

        puts "\ndone. #{line_idx} #{updated_count}"

end
#clear_money_by_excel 'fc-lt-import-base.xlsx'

def create_workout recs

    #产生表头
    headers = []
    names = []

    rec = recs[0]
    rec.each do |key, value| 
        headers += [key]
        names += [key]
    end

    save_to_excel headers,names,recs,"fc-lt-import-base.xls"

end
#create_workout get_recs

def freeze_other_money
    sql = "select u.uid, u.real_name, u.account, u.now_money, u.integral, from_unixtime(u.add_time) as created_at, l.level_id from  crmeb.eb_user u left join crmeb.eb_user_level l on u.uid = l.uid where u.now_money > 0 order by u.now_money desc"
    recs = []
    res = queryRds sql
    res.each do |r|
        recs += [r]
        money = r['now_money']
        bonus = r['integral']
        uid = r['uid']
        if money > 0
            sqls = []
            sqls += ["INSERT INTO crmeb.eb_user_bill(`uid`, `link_id`, `pm`, `title`, `category`, `type`, `number`, `balance`, `mark`, `add_time`, `status`, `take`) VALUES (#{uid}, '77777', 0, '迁移冻结请联系18998382701在乐土新商店解冻', 'now_money', 'system_sub', #{money}, 0.00, '迁移冻结到乐土新店扣除#{money}元余额', #{Time.now.to_i}, 1, 0);"]
            sqls += ["update crmeb.eb_user set now_money =0 where uid = #{uid};"]
            commitTrans sqls
            print '.'
        end
        if bonus > 0
            usql = "INSERT INTO crmeb.eb_user_bill (`uid`, `link_id`, `pm`, `title`, `category`, `type`, `number`, `balance`, `mark`, `add_time`, `status`, `take`) VALUES (#{uid}, '77777', 0, '迁移冻结请联系18998382701在乐土新商店解冻', 'integral', 'system_sub', #{bonus}, 0.00, '迁移到乐土新点冻结#{bonus}积分', #{Time.now.to_i}, 1, 0);"
            queryRds usql
            usql = "update crmeb.eb_user set integral =0 where uid = #{uid}"
            queryRds usql
            print('.')
        end
    end
    headers = []
    names = []
    recs[0].each { |k,v| headers += [k]; names += [k] }
    save_to_excel headers,names,recs,"fc-lt-frozen-base-1.xls"
end
#freeze_other_money
